﻿<%@ Page Language="VB" Debug="true" %>
<%@ Register TagPrefix="wmx" Namespace="Microsoft.Matrix.Framework.Web.UI" Assembly="Microsoft.Matrix.Framework, Version=0.6.0.0, Culture=neutral, PublicKeyToken=6f763c9966660626" %>
<%@ import Namespace="System.Data" %>
<%@ import Namespace="System.Data.OleDb" %>
<script runat="server">

    Sub Page_Load()
        Painel.Text = ""
        select (Request("acao"))
        case "sincronizar":
            Sincronizar(Request.Form("num"))
            Painel.Text = Painel.Text & "Pedidos sincronizados."
        case "listar":
            Painel.Text = Painel.Text & "<h3>Pedidos em Jolitex " & Request("num") & "</h3>"
            Lista(Request("num"))
        case else:
            Painel.Text = Painel.Text & "<h3>Escolha os pedidos que deseja sincronizar:</h3>"
            Painel.Text = Painel.Text & "<a href='sincronizar.aspx?num=31&acao=listar'>Jolitex 31</a><br>"
            Painel.Text = Painel.Text & "<a href='sincronizar.aspx?num=32&acao=listar'>Jolitex 32</a>"
        end select
    End Sub
    
    Sub Sincronizar(num as integer)
        Dim cnnJolitex As New OleDbConnection(ConfigurationSettings.AppSettings("jolitex" & num))
        Dim cnn as New OleDbConnection(ConfigurationSettings.AppSettings("strcnn"))
        Dim cmdJolitex as New OleDbCommand
        Dim cmd as New OleDbCommand
        Dim DR as OleDbDataReader
        Dim vet = Request.Form("id").Split(";")
        Dim i
    
        'Inicializacoes
        cnnJolitex.Open()
        cnn.Open()
        cmdJolitex.Connection = cnnJolitex
        cmd.Connection = cnn
    
        'Insere pedidos
        for each i in vet
            if i <> "" then
                cmd.CommandText = "select count(*) as cont from pedido where codigojolitex=" & i
                DR = cmd.ExecuteReader()
                DR.Read()
                Dim cont as integer = DR("cont")
                DR.Close()
                if cont <> 0 then
                    Painel.Text = Painel.Text & "<font color='red'><b>Erro: Pedido " & i & " já sincronizado.</b></font><br>"
                else
                    dim vetor
                    vetor = Request.Form("data" & i).Split("/")
                    dim data as String = vetor(1) & "/" & vetor(0) & "/" & vetor(2)
                    cmd.CommandText = "INSERT INTO Pedido ( IDCliente, IDVendedor, IDFabrica, IDTransportadora, Data, CondicaoPagamento, PrazoEntrega, Observacao, Desconto, Faturado, Cancelado, Total, CodigoJolitex, Sincronizado ) values (" & Request.Form("idcliente" & i) & ", " & Request.Form("idvendedor" & i) & ", " & Request.Form("idfabrica" & i) & ", " & Request.Form("idtransportadora" & i) & ", #" & data & "#, '" & Request.Form("CondicaoPagamento" & i) & "', '" & Request.Form("PrazoEntrega" & i) & "', '" & Request.Form("Observacao" & i) & "', '" & Request.Form("Desconto" & i) & "', false, false, 0, " & i & ", true)"
                    cmd.ExecuteNonQuery()
                    cmd.CommandText = "select max(id) as id from pedido"
                    DR = cmd.ExecuteReader()
                    DR.Read()
                    Dim id = DR("id")
                    Dim total = 0
                    Dim pecas = 0
                    DR.Close()
                    cmdJolitex.CommandText = "SELECT contListaPed.Qtde AS Quantidade, contListaPed.CodItem AS Codigo, [contListaPed]![Linha] & ' - ' & [contListaPed]![Modelo] & ' - ' & [contListaPed]![Tipo] AS Descricao, contListaPed.ValorUnit AS PrecoUnitario FROM contListaPed WHERE contListaPed.NumPedido=" & i
                    DR = cmdJolitex.ExecuteReader()
                    while DR.Read()
                        total = total + (DR("Quantidade") * DR("PrecoUnitario") * iif(instr(lcase(DR("descricao")), "tap") or instr(lcase(DR("descricao")), "banh"), 1.1, 1))
                        pecas = pecas + DR("Quantidade")
                        cmd.CommandText = "insert into produto (idpedido, quantidade, codigo, descricao, precounitario) values (" & id & ", " & DR("Quantidade") & ", '" & DR("Codigo") & "', '" & DR("Descricao") & "', " & Replace(DR("PrecoUnitario"), ",", ".") & ")"
                        cmd.ExecuteNonQuery()
                    end while
                    DR.Close()
                    total = (1 - (Request.Form("Desconto" & i)/100)) * total
                    cmd.CommandText = "update pedido set pecas=" & pecas & ", total=" & Replace(total, ",", ".") & " where id=" & id
                    cmd.ExecuteNonQuery()
                    Painel.Text = Painel.Text & "Pedido " & i & " sincronizado com sucesso.</b></font><br>"
                end if
            end if
        next
    
        'Fecha os acessos as bases de dados
        cnnJolitex.Close()
        cnn.Close()
    End Sub
    
    Sub Lista(num as integer)
        Dim cnnJolitex As New OleDbConnection(ConfigurationSettings.AppSettings("jolitex" & num))
        Dim cnn as New OleDbConnection(ConfigurationSettings.AppSettings("strcnn"))
        Dim cmdJolitex as New OleDbCommand
        Dim cmd as New OleDbCommand
        Dim DRJolitex as OleDbDataReader
        Dim DR as OleDbDataReader
        Dim ListaFabrica = ""
        Dim ListaVendedor = ""
        Dim ListaTransportadora = ""
    
        'Inicializacoes
        cnnJolitex.Open()
        cnn.Open()
        cmdJolitex.Connection = cnnJolitex
        cmd.Connection = cnn
    
        'Gera lista de fabricas
        cmd.CommandText = "select id, razaosocial from fabrica where razaosocial like '%jolitex%' order by razaosocial asc"
        DR = cmd.ExecuteReader()
        while DR.Read()
            ListaFabrica = ListaFabrica & "<option value='" & DR("id") & "'>" & DR("razaosocial") & "</option>"
        end while
        DR.Close()
    
        'Gera lista de vendedores
        cmd.CommandText = "select id, nome from vendedor order by nome asc"
        DR = cmd.ExecuteReader()
        while DR.Read()
            ListaVendedor = ListaVendedor & "<option value='" & DR("id") & "'>" & DR("nome") & "</option>"
        end while
        DR.Close()
    
        'Gera lista de transportadoras
        cmd.CommandText = "select id, razaosocial from transportadora order by razaosocial asc"
        DR = cmd.ExecuteReader()
        while DR.Read()
            ListaTransportadora = ListaTransportadora & "<option value='" & DR("id") & "'>" & DR("razaosocial") & "</option>"
        end while
        DR.Close()
    
        'Query dos pedidos em Jolitex
        cmdJolitex.CommandText = "SELECT contCabec.NumPedido, Cliente.Codigo, contCabec.Data_Pedido, contCabec.Condicao_Pagamento, contCabec.Entrega_Prevista, contCabec.Bonificacao FROM contCabec INNER JOIN Cliente ON contCabec.CGC = Cliente.CGC"
        DRJolitex = cmdJolitex.ExecuteReader()
    
        'Form de insercao de dados na sincronizacao
        Painel.Text = Painel.Text & "<form method='post' action='sincronizar.aspx'>"
        Painel.Text = Painel.Text & "<input type='hidden' name='acao' value='sincronizar'>"
        Painel.Text = Painel.Text & "<input type='hidden' name='num' value='" & num & "'>"
        Painel.Text = Painel.Text & "<table width='100%' border='0' cellspacing='2' cellpadding='2'>"
    
        'Loop na Query dos pedidos Jolitex
        Dim cor as boolean
        Dim ListaID = ""
        while (DRJolitex.Read())
            cmd.CommandText = "select id, razaosocial from cliente where codigojolitex='" & DRJolitex("codigo") & "'"
            DR = cmd.ExecuteReader()
            if DR.Read() then
                'Existe o cliente
                ListaID = ListaID & ";" & DRJolitex("numpedido")
                Painel.Text = Painel.Text & "<tr bgcolor='" & iif(cor, "#ADD8E6", "#E0FFFF") & "'><td><table border='0' cellpadding='4'>"
                Painel.Text = Painel.Text & "<tr><td>Cliente:</td><td>" & DR("razaosocial") & "</td></tr>"
                Painel.Text = Painel.Text & "<tr><td><input type='hidden' name='idcliente" & DRJolitex("numpedido") & "' value='" & DR("id") & "'>Número do Pedido:</td><td>" & DRJolitex("NumPedido") & "</td></tr>"
                Painel.Text = Painel.Text & "<tr><td><input type='hidden' name='data" & DRJolitex("numpedido") & "' value='" & DRJolitex("Data_Pedido") & "'>Data:</td><td>" & DRJolitex("Data_Pedido") & "</td></tr>"
                Painel.Text = Painel.Text & "<tr><td><input type='hidden' name='condicaopagamento" & DRJolitex("numpedido") & "' value='" & DRJolitex("Condicao_Pagamento") & "'>Condição de pagamento:</td><td>" & DRJolitex("Condicao_Pagamento") & "</td></tr>"
                Painel.Text = Painel.Text & "<tr><td><input type='hidden' name='prazoentrega" & DRJolitex("numpedido") & "' value='" & DRJolitex("Entrega_Prevista") & "'>Prazo de entrega:</td><td>" & DRJolitex("Entrega_Prevista") & "</td></tr>"
                Painel.Text = Painel.Text & "<tr><td><input type='hidden' name='desconto" & DRJolitex("numpedido") & "' value='" & DRJolitex("Bonificacao") & "'>Desconto:</td><td>" & DRJolitex("Bonificacao") & "</td></tr>"
                Painel.Text = Painel.Text & "<tr><td>Fábrica:</td><td><select name='idfabrica" & DRJolitex("numpedido") & "'>" & ListaFabrica & "</select></td></tr>"
                Painel.Text = Painel.Text & "<tr><td>Vendedor:</td><td><select name='idvendedor" & DRJolitex("numpedido") & "'>" & ListaVendedor & "</select></td></tr>"
                Painel.Text = Painel.Text & "<tr><td>Transportadora:</td><td><select name='idtransportadora" & DRJolitex("numpedido") & "'>" & ListaTransportadora & "</select></td></tr>"
                Painel.Text = Painel.Text & "<tr><td valign='top'>Observação:</td><td><textarea rows='5' cols='30' name='observacao" & DRJolitex("numpedido") & "'></textarea></td></tr>"
                Painel.Text = Painel.Text & "</table></td></tr>"
            else
                'O cliente nao existe
                Painel.Text = Painel.Text & "<tr bgcolor='" & iif(cor, "#ADD8E6", "#E0FFFF") & "'><td colspan='8'><b><font color='red'>Erro: cliente " & DRJolitex("Codigo") & " não consta no sistema.</font></b></td></tr>"
            end if
            cor = not cor
            DR.close()
        End while
    
        'Fim do form de insercao de dados
        Painel.Text = Painel.Text & "</table>"
        Painel.Text = Painel.Text & "<input type='hidden' name='id' value='" & ListaID & "'>"
        Painel.Text = Painel.Text & "<input type='submit' value='Sincronizar'>"
        Painel.Text = Painel.Text & "</form>"
    
        'Fecha os acessos as bases de dados
        DRJolitex.Close()
        cnnJolitex.Close()
        cnn.Close()
    End Sub

</script>
<html>
<head>
</head>
<body style="FONT-FAMILY: arial">
    <table bordercolor="navajowhite" cellspacing="0" cellpadding="2" width="950" align="center" bgcolor="floralwhite">
        <tbody>
            <tr>
                <td valign="center" align="middle" bgcolor="#ffdead" colspan="2">
                    <h2>Sincronizar 
                    </h2>
                </td>
            </tr>
            <tr>
                <td valign="top">
                    <asp:Label id="Painel" runat="server"></asp:Label></td>
            </tr>
        </tbody>
    </table>
</body>
</html>
